package com.scuec.groupfive.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;

@Mapper
public interface SubjectCorrectRateMapper {

    /**
     * 获取各科目答题正确率统计
     * @return 包含科目名称、答题总数、正确数和正确率的列表
     */
    @Select("SELECT " +
            "s.subject_name AS subjectName, " +
            "COUNT(*) AS totalAnswers, " +
            "SUM(CASE WHEN ar.is_correct = 1 THEN 1 ELSE 0 END) AS correctAnswers, " +
            "ROUND(SUM(CASE WHEN ar.is_correct = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS correctRate " +
            "FROM answer_records ar " +
            "JOIN questions q ON ar.question_id = q.question_id " +
            "JOIN subjects s ON q.subject_id = s.subject_id " +
            "GROUP BY s.subject_id, s.subject_name")
    List<Map<String, Object>> getSubjectCorrectRates();
}